package way;

import common.Utils.UUIDGenerator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * mysql:使用.xlsx导入数据
 */
public class Mysql {

    public static void main(String[] args) throws IOException {
        //获取本地文件
        File file = new File("D:\\eloan.xlsx");
        FileInputStream stream = new FileInputStream(file);
        Workbook workbook = new XSSFWorkbook(stream);
        Sheet sheet = workbook.getSheetAt(0);
        int i = 0;
        //循环赋值
        while (sheet.getRow(i)!=null){
            Row row = sheet.getRow(i);

            String id = UUIDGenerator.generate();
            String DATA_TYPE_NO = row.getCell(0).getStringCellValue();
            String DATA_TYPE_NAME = row.getCell(1).getStringCellValue();
            String DATA_NO = row.getCell(2).getStringCellValue();
            String DATA_NAME = row.getCell(3).getStringCellValue();
            //调用方法链接，新增
            link(id,DATA_TYPE_NO,DATA_TYPE_NAME,DATA_NO,DATA_NAME);
            i++;
        }


    }

    /**
     * 连接数据库，传参
     * @param id
     * @param DATA_TYPE_NO
     * @param DATA_TYPE_NAME
     * @param DATA_NO
     * @param DATA_NAME
     * @return
     */
    public static boolean link(String id,String DATA_TYPE_NO,String DATA_TYPE_NAME,String DATA_NO,String DATA_NAME ){

        Connection conn;
        CallableStatement st;
        //jdbc驱动
        String driver = "com.mysql.cj.jdbc.Driver";
        //这里我的数据库是cxxt
        String url = "jdbc:mysql://localhost:3306/stardust?&useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "yinpumei";

        try {
            //注册JDBC驱动程序
            Class.forName(driver);
            //建立连接
            conn = DriverManager.getConnection(url,user,password);
//            if (!conn.isClosed()) {
//                System.out.println("数据库连接成功");
//            }
            //新增数据
            st = conn.prepareCall("insert into DIC(ID,DATA_TYPE_NO,DATA_TYPE_NAME,DATA_NO,DATA_NAME) VALUES(?,?,?,?,?)");//这里是执行插入操作，可以换成别的如UPDATE

            st.setString(1, id);
            st.setString(2, DATA_TYPE_NO);
            st.setString(3, DATA_TYPE_NAME);
            st.setString(4, DATA_NO);
            st.setString(5, DATA_NAME);
            st.execute();
            //关闭连接
            conn.close();
        } catch (ClassNotFoundException e) {
            System.out.println("数据库驱动没有安装");
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            System.out.println("数据库连接失败");
        }

        return false;
    }

}
